Inner Join

The most important and frequently used of the joins is the INNER JOIN. They are also referred to as an EQUIJOIN. The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

Syntax

The basic syntax of the INNER JOIN is as follows.

SELECT 

 t1.column1

,t2.column2...
FROM table1 t1 INNER JOIN table2 t2
ON table1.common_field = table2.common_field;

Example

n this syntax, the query retrieved data from both T1 and T2 tables:

• First, specify the main table (T1) in the FROM clause
• Second, specify the second table in the INNER JOIN clause (T2) and a join predicate. Only rows that cause the join predicate to evaluate to TRUE are included in the result set.

The INNER JOIN clause compares each row of the table T1 with rows of table T2 to find all pairs of rows that satisfy the join predicate. If the join predicate evaluates to TRUE, the column values of the matching rows of T1 and T2 are combined into a new row and included in the result set. The following table illustrates the inner join of two tables T1 (1,2,3) and T2 (A,B,C). The result includes rows: (2,A) and (3,B) as they have the same patterns.

SELECT
    product_name,
    category_name,
    brand_name,
    list_price
FROM
    production.products p
INNER JOIN production.categories c ON c.category_id = p.category_id
INNER JOIN production.brands b ON b.brand_id = p.brand_id
ORDER BY
    product_name DESC;